#! /bin/bash
# ods_to_dim_init.sh all/表名
#1、判断参数是否传入
if [ $# -lt 1 ]
then
	echo "必须至少传入一个表名/all..."
	exit
fi

#2、首日日期是固定的
datestr="2022-06-14"

## 1、dim_user_zip
dim_user_zip_sql="
set hive.execution.engine=mr;
insert overwrite table dim_user_zip partition (dt='9999-12-31')
select data.id           id,
       data.login_name   login_name,
       data.nick_name    nick_name,
       data.real_name    real_name,
       data.phone_num    phone_num,
       data.email        email,
       data.user_level   user_level,
       data.birthday     birthday,
       data.gender       gender,
       data.create_time  create_time,
       data.operate_time operate_time,
       --如果修改过,则生效日期为最后一次的修改日期,如果没有修改,则生效日期为注册日期
       date_format(nvl(data.operate_time,data.create_time),'yyyy-MM-dd') start_date,
       '9999-12-31' end_date
from ods_user_info_inc
where dt='$datestr' and type = 'bootstrap-insert';"

##2 dim_base_category_info_full
dim_base_category_info_full_sql="
insert overwrite table dim_base_category_info_full partition(dt='$datestr')
select
    id,
    category_name,
    create_time,
    update_time,
    deleted
from ods_base_category_info_full
where dt='$datestr';"

##3dim_chapter_full
dim_chapter_full_sql="insert overwrite table dim_chapter_full partition(dt='$datestr')
select
  chapter_id    ,
  chapter_name ,
  video_id     ,
  video_name    ,
  course_id     ,
  course_name   ,
  subject_id,
  subject_name,
  category_id,
  category_name,
  during_sec    ,
  is_free       ,
  create_time   ,
  update_time   ,
  deleted
from (
         select
                id chapter_id,
                chapter_name,
                course_id,
                video_id,
                create_time,
                update_time,
                is_free,
                deleted
         from ods_chapter_info_full
         where dt = '$datestr'
     ) chapter
left join
     (
         select
                id e_video_id,
                video_name,
                during_sec
         from ods_video_info_full
         where dt = '$datestr'
     ) video
on chapter.video_id = video.e_video_id
        left join
    (
        select
                id e_course_id,
                course_name,
                subject_id
        from ods_course_info_full
        where dt='$datestr'
    ) course
on chapter.course_id = course.e_course_id
left join
    (
        select
                id e_subject_id,
                subject_name,
                category_id
        from ods_base_subject_info_full
        where dt='$datestr'
    ) subject
on course.subject_id = subject.e_subject_id
left join
(
    select
            id  e_category_id,
            category_name
    from ods_base_category_info_full
    where dt='$datestr'
) category
on subject.category_id = category.e_category_id;"

##4 dim_paper_full partition
dim_paper_full_sql="with tp as (
    select id,
           paper_title,
           course_id
    from ods_test_paper_full
    where dt = '$datestr'
),tpq as (
    select paper_id,
           question_id,
           score
    from ods_test_paper_question_full
    where dt = '$datestr'
),te as (
    select paper_id,
           user_id,
           score,
           duration_sec
    from ods_test_exam_full
    where dt = '$datestr'
),teq as (
    select data.paper_id,
           data.exam_id,
           data.answer,
           data.is_correct,
           data.score
    from ods_test_exam_question_inc
    where dt = '$datestr' and type = 'bootstrap-insert'
) insert overwrite table dim_paper_full partition (dt ='$datestr')
select tp.id,
       tp.paper_title,
       tp.course_id,
       tpq.question_id,
       tpq.score,
       te.user_id,
       te.score,
       te.duration_sec,
       teq.exam_id,
       teq.answer,
       teq.is_correct,
       teq.score
from tp left join tpq on tp.id = tpq.paper_id
        left join te on tp.id = te.paper_id
        left join teq on tp.id = teq.paper_id;"

##5 dim_subject_full
dim_subject_full_sql="insert overwrite table dim_subject_full partition(dt='$datestr')
select
    subject.id,subject_name,category_id,
       subject.create_time,subject.update_time,
       subject.deleted
from
(
    select
        id,
        subject_name,
        category_id,
        create_time,
        update_time,
        deleted
    from ods_base_subject_info_full
    where dt='$datestr'
)subject
left join
(
    select
        id,
        category_name,
        create_time,
        update_time,
        deleted
    from ods_base_category_info_full
    where dt='$datestr'
)category
on subject.category_id= category.id;"

##6
dim_course_full_sql="insert overwrite table dim_course_full partition (dt = '$datestr')
select t1.id,
       course_name,
       course_slogan,
       course_cover_url,
       subject_id,
       teacher,
       publisher_id,
       chapter_num,
       origin_price,
       reduce_amount,
       actual_price,
       course_introduce,
       create_time,
       update_time,
       deleted,
       comment_txt,
       review_txt,
       review_stars
from (
         select id,
                course_name,
                course_slogan,
                course_cover_url,
                subject_id,
                teacher,
                publisher_id,
                chapter_num,
                origin_price,
                reduce_amount,
                actual_price,
                course_introduce,
                create_time,
                update_time,
                deleted
         from ods_course_info_full t1
         where dt = '$datestr'
         ) t1
         left join
     ( --课程评价表
         select data.course_id    course_id,
                data.review_txt   review_txt,
                data.review_stars review_stars
         from ods_review_info_inc
         where dt = '$datestr' and type = 'bootstrap-insert'
     ) t2
     on t1.id = t2.course_id
         left join
     ( --章节评价表
         select data.course_id   course_id,
                data.comment_txt comment_txt
         from ods_comment_info_inc
         where dt = '$datestr' and type = 'bootstrap-insert'
     ) t3
     on t2.course_id = t3.course_id;"

##7dim_province_full
dim_province_full_sql="insert overwrite table dim_province_full partition(dt='$datestr')
select
	id,
	name,
	region_id,
	area_code,
	iso_code,
	iso_3166_2
from ods_base_province_full
where dt='$datestr';"

##8 dim_question_full
dim_question_full_sql="insert overwrite table dim_question_full partition(dt='$datestr')
select
   question_id,
    point_id,
    option_txt,
    paper_id,
 question_txt,
    chapter_id,
    create_time,
    update_time,
    question_type,
    publisher_id,
    deleted,
    course_id
from (
	select
	id ,
  	option_txt,
   	 question_id
	from edu.ods_test_question_option_full
 	where dt='$datestr'
) tqo
left join
(
select
    id,
    question_txt ,
    chapter_id ,
    create_time  ,
    update_time,
    question_type ,
    publisher_id ,
    deleted ,
    course_id
		from edu.ods_test_question_info_full
		 where dt='$datestr'


) tql
on tql.id=tqo.id
left join
(
	select
  		 id ,
		point_id
	from	edu.ods_test_point_question_full
		 where dt='$datestr'

)	tpq
on tql.id = tpq.id
left join
(
		select
		   id   ,
    		paper_id
		from edu.ods_test_paper_question_full
		 where dt='$datestr'
)	paper_question
on tql.id=	paper_question.id;"


#3、根据第一个参数匹配加载
case $1 in
"all")
	/opt/module/hive/bin/hive -e "use edu;${dim_user_zip_sql};"
	/opt/module/hive/bin/hive -e "use edu;${dim_base_category_info_full_sql};"
	/opt/module/hive/bin/hive -e "use edu;${dim_chapter_full_sql};"
	/opt/module/hive/bin/hive -e "use edu;${dim_paper_full_sql};"
    /opt/module/hive/bin/hive -e "use edu;${dim_subject_full_sql};"
    /opt/module/hive/bin/hive -e "use edu;${dim_course_full_sql};"
    /opt/module/hive/bin/hive -e "use edu;${dim_province_full_sql};"
    /opt/module/hive/bin/hive -e "use edu;${dim_question_full_sql};"
;;
"dim_user_zip")
	/opt/module/hive/bin/hive -e "use edu;${dim_user_zip_sql};"
;;
"dim_base_category_info_full")
	/opt/module/hive/bin/hive -e "use edu;${dim_base_category_info_full_sql};"
;;
"dim_chapter_full")
	/opt/module/hive/bin/hive -e "use edu;${dim_chapter_full_sql};"
;;
"dim_paper_full")
	/opt/module/hive/bin/hive -e "use edu;${dim_paper_full_sql};"
;;
"dim_subject_full")
	/opt/module/hive/bin/hive -e "use edu;${dim_subject_full_sql};"
;;
"dim_course_full")
	/opt/module/hive/bin/hive -e "use edu;${dim_course_full_sql};"
;;
"dim_province_full")
	/opt/module/hive/bin/hive -e "use edu;${dim_province_full_sql};"
;;
"dim_question_full")
	/opt/module/hive/bin/hive -e "use edu;${dim_question_full_sql};"
;;
*)
	echo "表名传入错误..."
;;
esac





